"""
Case Type   : 行存压缩
Case Name   : 二级分区表非压缩表，修改为压缩表，使用gs_probackup备份、恢复，前后数据一致
Create At   : 2023/8/9
Owner       : li-xin12345
Description :
    1、建二级分区表（非压缩表），并插入数据
    2、修改二级分区表为压缩表
    3、查看表占用磁盘大小、数据量等
    4、新建目录，进行初始化
    5、在备份路径内初始化一个新的备份实例
    6、进行全量备份
    7、删除表，停止数据库
    8、执行restore，并重启数据库
    9、再次查看表占用磁盘大小、数据量等，与备份前对比
    10、清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
    5、成功
    6、成功
    7、成功
    8、成功
    9、与备份前一致
    10、成功
History     :
"""

import os
import unittest
import re

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

Pri_U_SH = CommonSH('PrimaryDbUser')
Node_Num = Pri_U_SH.get_node_num()


@unittest.skipIf(3 > Node_Num, '不满足一主两备环境跳过')
class RowCompressTest(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.std1_sh = CommonSH('Standby1DbUser')
        self.std2_sh = CommonSH('Standby2DbUser')
        self.pri_user = Node('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.tb_name = 't_alter_rowcompress_0061'
        self.backup_dir = 'f_aler_rowcompress_0061'
        self.backup_path = os.path.join(os.path.dirname(
            macro.DB_INSTANCE_PATH), self.backup_dir)
        self.alter_compress_param = 'compresstype=2, ' \
                                    'compress_chunk_size=4096, ' \
                                    'compress_prealloc_chunks=1, ' \
                                    'compress_byte_convert=true, ' \
                                    'compress_diff_convert=true, ' \
                                    'compress_level=15'

    def test_row_compress(self):
        text = '-----step1:建二级分区表（非压缩表），并插入数据;expect:成功-----'
        self.log.info(text)
        sql = f'''drop table if exists {self.tb_name} cascade;
        create table {self.tb_name}
        (
            month_code varchar2 ( 30 ) not null ,
            dept_code  varchar2 ( 30 ) not null ,
            user_no    varchar2 ( 30 ) not null ,
            sales_amt  int
        )
        partition by list (month_code) subpartition by list (dept_code)
        (
          partition p_201901 values ( '201902' )
          (
            subpartition p_201901_a values ( '1' ),
            subpartition p_201901_b values ( '2' )
          ),
          partition p_201902 values ( '201903' )
          (
            subpartition p_201902_a values ( '1' ),
            subpartition p_201902_b values ( '2' )
          )
        );
        insert into {self.tb_name} values('201902', '1', '1', 1);
        insert into {self.tb_name} values('201902', '2', '1', 1);
        insert into {self.tb_name} values('201902', '1', '1', 1);
        insert into {self.tb_name} values('201903', '2', '1', 1);
        insert into {self.tb_name} values('201903', '1', '1', 1);
        insert into {self.tb_name} values('201903', '2', '1', 1);
        '''
        sql_res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(sql_res)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, sql_res,
                      '执行失败：' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_res,
                      '执行失败：' + text)
        self.assertIn('INSERT 0 1', sql_res, '执行失败：' + text)
        self.assertEqual(sql_res.count('INSERT 0 1'), 6, '执行失败：' + text)

        text = '-----step2:修改二级分区表为压缩表;expect:成功-----'
        self.log.info(text)
        sql = f'alter table {self.tb_name} set ' \
              f'({self.alter_compress_param}); checkpoint;' \
              f'\\d+ {self.tb_name}'
        sql_res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(sql_res)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_res,
                      '执行失败：' + text)
        self.assertIn(self.constant.CHECKPOINT_SUCCESS_MSG, sql_res,
                      '执行失败：' + text)
        self.assertIn(f'{self.alter_compress_param}', sql_res,
                      '执行失败：' + text)

        text = '-----step3:查看表占用磁盘大小、数据量等-----'
        self.log.info(text)
        sql = f"select pg_size_pretty(pg_total_relation_size(" \
              f"'{self.tb_name}')) as table_size;"
        res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(res)
        self.assertNotIn('ERROR', res, '执行失败：' + text)

        sql = f'select checksum({self.tb_name}::text) from {self.tb_name};'
        before_size = Pri_U_SH.execut_db_sql(sql)
        self.log.info(before_size)
        self.assertNotIn('ERROR', before_size, '执行失败：' + text)

        text = '-----step4:新建目录，进行初始化-----'
        self.log.info(text)
        cmd = f"rm -rf {self.backup_path};mkdir -p {self.backup_path};" \
              f"chmod 700 {self.backup_path};" \
              f"ls {os.path.dirname(macro.DB_INSTANCE_PATH)} | " \
              f"grep '{self.backup_dir}';"
        self.log.info(cmd)
        res = self.common.get_sh_result(self.pri_user, cmd)
        self.assertIn(f'{self.backup_dir}', res, '执行失败：' + text)

        cmd = f'source {macro.DB_ENV_PATH};' \
              f'gs_probackup init -B {self.backup_path}'
        self.log.info(cmd)
        res = self.common.get_sh_result(self.pri_user, cmd)
        self.assertIn('successfully inited', res, '执行失败：' + text)

        self.log.info('-----step5:在备份路径内初始化一个新的备份实例;'
                      'expect:成功-----')
        cmd = f"source {macro.DB_ENV_PATH};" \
              f"gs_probackup add-instance -B {self.backup_path} " \
              f"-D {macro.DB_INSTANCE_PATH} --instance=pro1"
        self.log.info(cmd)
        res = self.common.get_sh_result(self.pri_user, cmd)
        self.assertIn('successfully inited', res, '执行失败：' + text)

        self.log.info('-----step6:进行全量备份;expect:成功-----')
        cmd = f"source {macro.DB_ENV_PATH};" \
              f"gs_probackup backup -B {self.backup_path} -b full --stream " \
              f"--instance=pro1 --skip-block-validation " \
              f"--no-validate -p {self.pri_user.db_port} " \
              f"-d {self.pri_user.db_name}"
        self.log.info(cmd)
        res = self.common.get_sh_result(self.pri_user, cmd)
        re.search('Backup * completed', res)

        self.log.info('-----查看备份情况-----')
        cmd = f"source {macro.DB_ENV_PATH};" \
              f"gs_probackup show -B {self.backup_path} --instance=pro1"
        self.log.info(cmd)
        res = self.common.get_sh_result(self.pri_user, cmd)
        self.assertIn('DONE', res, '执行失败：' + text)

        self.log.info('-----step7:删除表，停止数据库;expect:成功-----')
        sql = f"drop table if exists {self.tb_name};"
        self.log.info(sql)
        res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res,
                      '执行失败：' + text)

        stop_res = Pri_U_SH.stop_db_instance()
        self.assertIn(self.constant.GS_CTL_STOP_SUCCESS_MSG, stop_res,
                      '执行失败：' + text)

        self.log.info('-----step8:执行restore，并重启数据库;expect:成功-----')
        cmd = f"source {macro.DB_ENV_PATH};" \
              f"gs_probackup restore -B {self.backup_path} --instance=pro1 " \
              f"--incremental-mode=checksum -D {macro.DB_INSTANCE_PATH};"
        self.log.info(cmd)
        res = self.common.get_sh_result(self.pri_user, cmd)
        re.search('Restore of backup * completed', res)

        start_res = Pri_U_SH.start_db_instance()
        self.assertIn('server started', start_res, '启动失败')

        status = Pri_U_SH.get_db_cluster_status(param='status')
        self.log.info(status)
        if status is False:
            build_res1 = self.std1_sh.build_standby(mode='')
            self.assertIn(self.constant.BUILD_SUCCESS_MSG, build_res1)
            build_res2 = self.std2_sh.build_standby(mode='')
            self.assertIn(self.constant.BUILD_SUCCESS_MSG, build_res2)
            status = Pri_U_SH.get_db_cluster_status(param='status')
            self.log.info(status)

        text = '-----step9:再次查看表占用磁盘大小、数据量等，与备份前对比;' \
               'expect:查看数据与备份前一致-----'
        self.log.info(text)
        sql = f"select pg_size_pretty(pg_total_relation_size(" \
              f"'{self.tb_name}')) as table_size;"
        res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(res)
        self.assertNotIn('ERROR', res, '执行失败：' + text)

        sql = f'select checksum({self.tb_name}::text) from {self.tb_name};'
        after_size = Pri_U_SH.execut_db_sql(sql)
        self.log.info(after_size)
        self.assertNotIn('ERROR', before_size, '执行失败：' + text)
        self.assertEqual(before_size, after_size, '执行失败：' + text)

    def tearDown(self):
        text = '-----step10:清理环境; expect:成功-----'
        self.log.info(text)
        cmd = f"source {macro.DB_ENV_PATH};" \
              f"gs_probackup del-instance -B {self.backup_path} " \
              f"-D {macro.DB_INSTANCE_PATH} --instance=pro1"
        del_res = self.common.get_sh_result(self.pri_user, cmd)
        self.log.info(del_res)

        cmd = f'rm -rf {self.backup_path};' \
              f'if [ -d {self.backup_path} ]; ' \
              f'then echo "exists"; else echo "not exists"; fi'
        rm_res = self.common.get_sh_result(self.pri_user, cmd)
        self.log.info(rm_res)

        sql = f'drop table if exists {self.tb_name};'
        drop_res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(drop_res)

        status = Pri_U_SH.get_db_cluster_status(param='status')
        self.log.info(status)
        if status is False:
            build_res1 = self.std1_sh.build_standby(mode='')
            self.assertIn(self.constant.BUILD_SUCCESS_MSG, build_res1)
            build_res2 = self.std2_sh.build_standby(mode='')
            self.assertIn(self.constant.BUILD_SUCCESS_MSG, build_res2)
            status = Pri_U_SH.get_db_cluster_status(param='status')
            self.log.info(status)
        self.assertTrue(status)

        self.assertIn('successfully deleted', del_res, '备份实例删除失败')
        self.assertEqual('not exists', rm_res, '文件删除失败')
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, drop_res,
                      '执行失败：' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
